package com.yh.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.yh.dto.UserSearch;
import com.yh.entity.Zhanfeng;
import com.yh.util.DBUtil;
import com.yh.vo.UserVO;
import com.yh.vo.ZhanfengVO;

import cn.hutool.core.util.StrUtil;

/**
 * 对实体类zhanfeng的增删改查等操作
 * 
 * @author 湛锋
 *
 */
public class ZhanfengDAO implements DAOSupport<Zhanfeng> {

	@Override
	public int insert(Zhanfeng entity) throws SQLException {
		String sql = "INSERT INTO zhanfeng (id, zf_name, zf_password, zf_sex, zf_age, zf_time) VALUES (?,?,?,?,?,?)";
		try (Connection conn = DBUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
//			pstmt.setInt(1, entity.getZfId());
//			pstmt.setObject(1, entity.getZfId());//自动拆箱
//			pstmt.setObject(2, entity.getZfName());
//			pstmt.setObject(3, entity.getZfPassword());
			// ...

			DBUtil.setParams(pstmt, entity.getZfId(), entity.getZfName(), entity.getZfPassword(), entity.getZfSex(),
					entity.getZfAge(), entity.getZfTime());

			return pstmt.executeUpdate();
		}
	}

	@Override
	public int updateById(Zhanfeng entity) throws SQLException {
		String sql = "update zhanfeng set zf_name=?,zf_password=?,zf_sex=?,zf_age=?,zf_time=? where id=?";
		try (Connection conn = DBUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
			DBUtil.setParams(pstmt, entity.getZfName(), entity.getZfPassword(), entity.getZfSex(), entity.getZfAge(),
					entity.getZfTime(), entity.getZfId());

			return pstmt.executeUpdate();
		}

	}

	@Override
	public int deleteById(Serializable id) throws SQLException {
		String sql = "delete from zhanfeng where id=?";
		try (Connection conn = DBUtil.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql);) {
			pstmt.setObject(1, id);
			return pstmt.executeUpdate();
		}
	}

	@Override
	public Zhanfeng selectById(Serializable id) throws SQLException {
		String sql = "select * from zhanfeng where id=?";// 禁止使用*
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return load(rs);
			} else {
				throw new SQLException("没有对应的记录");
			}
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}

	@Override
	public List<Zhanfeng> select(int index, int len) throws SQLException {
		String sql = "select * from zhanfeng order by id limit ?,?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, index);
			pstmt.setObject(2, len);
			rs = pstmt.executeQuery();
			List<Zhanfeng> list = new ArrayList<>();
			while (rs.next()) {
				list.add(load(rs));
			}
			return list;
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}

	/**
	 * 根据传入的结果集对象返回查询到的实体对象
	 * 
	 * @param rs
	 * @return 实体对象
	 * @throws SQLException
	 */
	private Zhanfeng load(ResultSet rs) throws SQLException {
		Zhanfeng entity = new Zhanfeng();
		entity.setZfId(rs.getInt("id"));
		entity.setZfName(rs.getString("zf_name"));
		entity.setZfPassword(rs.getString("zf_password"));
		entity.setZfSex(rs.getString("zf_sex"));
		entity.setZfAge(rs.getInt("zf_age"));
		entity.setZfTime(rs.getTimestamp("zf_time"));
		return entity;
	}

	/**
	 * 统计zhanfeng表的记录总数
	 */
	@Override
	public long count() throws SQLException {
		String sql = "select count(*) as c from zhanfeng";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getLong(1);
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}

//	public List<ZhanfengVO> select(UserSearch search,int index,int len) throws SQLException{
//		StringBuffer sql = new StringBuffer("select * from zhanfeng");
//		List<Object> params = new ArrayList<Object>();
//		where(search, sql, params);
//		sql.append("ORDER BY"+search.getSortName()+" "+search.getSortOrder()+"LIMIT ?,?");
//		System.out.println("SQL:"+sql);//日志
//		System.out.println("patams:"+params);
//		return DBUtil.list(sql.toString(), UserVO.class, params.toArray());
//	}

	public long count(UserSearch search) throws SQLException {
		StringBuffer sql = new StringBuffer("SELECT count(*) as c from zhanfeng a");
		List<Object> params = new ArrayList<Object>();
		where(search, sql, params);
		System.out.println("SQL" + sql);
		System.out.println("params" + params);
		return DBUtil.getLong(sql.toString(), params.toArray());

	}

	public void where(UserSearch search, StringBuffer sql, List<Object> params) {
		if (!StrUtil.hasBlank(search.getKey())) {
			sql.append(" where a.zf_name like CONCAT('%',?,'%')");
			params.add(search.getKey());
		}
	}

	/**
	 * 根据关键字查询
	 * 
	 * @param key
	 * @throws SQLException
	 */
	public List<Zhanfeng> keySelect(String key, int index, int len) throws SQLException {
		String sql = "select * from zhanfeng where zf_name like CONCAT('%',?,'%') order by id limit ?,?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, key);
			pstmt.setObject(2, index);
			pstmt.setObject(3, len);
			rs = pstmt.executeQuery();
			List<Zhanfeng> list = new ArrayList<>();
			while (rs.next()) {
				list.add(load(rs));
			}
			return list;
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}
	
	/**
	 * 根据关键字来统计zhanfeng表中zf_name中含关键字的总记录数
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	public long keyCount(String key) throws SQLException {
		String sql = "select count(*) as c from zhanfeng where zf_name like CONCAT('%',?,'%')";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, key);
			rs = pstmt.executeQuery();
			rs.next();
			return rs.getLong(1);
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}

	/**
	 * 根据用户名查询用户
	 * @param userName
	 * @return
	 * @throws SQLException
	 */
	public Zhanfeng selectByName(String userName) throws SQLException {
		String sql = "select * from zhanfeng where zf_name=?";// 禁止使用*
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, userName);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return load(rs);
			} else {
				throw new SQLException("没有对应的记录");
			}
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}
	
	/**
	 * 根据关键字多表查询
	 * 
	 * @param key
	 * @throws SQLException
	 */
	public List<UserVO> keySelectTwo(String key, int index, int len) throws SQLException {
		String sql = "select a.*,b.ROLE_NAME from zhanfeng a inner join zhanfeng_role b on a.zf_role=b.ROLE_ID where zf_name like CONCAT('%',?,'%') order by id limit ?,?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, key);
			pstmt.setObject(2, index);
			pstmt.setObject(3, len);
			rs = pstmt.executeQuery();
			List<UserVO> list = new ArrayList<>();
			while (rs.next()) {
				UserVO entity = new UserVO();
				entity.setZfId(rs.getInt("id"));
				entity.setZfName(rs.getString("zf_name"));
				entity.setZfPassword(rs.getString("zf_password"));
				entity.setZfSex(rs.getString("zf_sex"));
				entity.setZfAge(rs.getInt("zf_age"));
				entity.setZfTime(rs.getTimestamp("zf_time"));
				entity.setZfRole(rs.getString("ROLE_NAME"));
				list.add(entity);
			}
			return list;
		} finally {
			DBUtil.close(conn, pstmt, rs);
		}
	}
	
}
